package model;

import helperclasses.DbUtil;
import helperclasses.Product;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.sql.DataSource;

public class ViewProductStock {
	ArrayList<Product> productsInStock = new ArrayList<Product>();

	public ViewProductStock() {

	}

	private Connection getConnection() throws SQLException {

		DataSource ds = DbUtil.getInstance().getDataSource();
		Connection conn = ds.getConnection();
		return conn;
	}

	public ArrayList<Product> getStock(String searchValue, String sort, boolean isCocktail) throws SQLException {
		Product product;
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		ResultSet result = null;
		String sql, cocktail;
		
		if (searchValue != null) {
			if (searchValue.equals("")) {
				searchValue = "";
			} else {
				searchValue = "AND p.nume LIKE \'%" + searchValue + "%\'";
			}
		} else {
			searchValue = "";
		}
		if(sort != null){
			if(sort.equals("name")){
				sort = " ORDER BY p.nume ";
			}
			else if(sort.equals("cat")){
				sort = " ORDER BY p.tip_nir, p.nume ";
			}
			else if(sort.equals("price")){
				sort = " ORDER BY p.pret_iesire DESC, p.nume";
			}
		}
		else sort = "";
		if(isCocktail)
			cocktail = " WHERE tip_nir=3 ";
		else
			cocktail = " WHERE (tip_nir=1 OR tip_nir=2 OR tip_nir=4) ";
		try {
			sql = "SELECT p.produs_id, p.nume, p.valoare, p.pret_iesire, p.tip_nir, ps.cantitate, um.tip " + " FROM produse p "
					+ " NATURAL JOIN produse_in_stoc ps " + " NATURAL JOIN unitate_masura um " + cocktail + searchValue + sort;
			result = st.executeQuery(sql);
			while (result.next()) {
				product = new Product();
				product.setProductId(Integer.parseInt(result.getString("produs_id")));
				product.setProductName(result.getString("nume"));
				product.setTipNir(Integer.parseInt(result.getString("tip_nir")));
				product.setValoare(Double.parseDouble(result.getString("valoare")));
				product.setPretIesire(Double.parseDouble(result.getString("pret_iesire")));
				product.setCantitate(Integer.parseInt(result.getString("cantitate")));
				product.setUnitateMasura(result.getString("tip"));

				productsInStock.add(product);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}
		return productsInStock;
	}
}
